<?php

$test_series = $_GET['test_series'];

include 'db_connection.php';
include 'navigation.php';

$view_name_baseline = "BEST_CASE_Baseline_$test_series";

$query1view = "create view $view_name_baseline as select Name, (MAX(Uptime) - MIN(Uptime))/(select COUNT(Name) from LOCKS_LOG where Series_id=$test_series and Type='Baseline' and Name='name') +1 as Best_case_baseline from LOCKS_LOG where Series_id=$test_series and Type='Baseline' group by Name order by Best_case_baseline DESC";
$QueryResult1view = @mysqli_query($DBConnect, $query1view) or die("<p>Unable to execute query1view.</p>");

$query1select = "select Name, Best_case_baseline from $view_name_baseline";
$QueryResult1select = @mysqli_query($DBConnect, $query1select) or die("<p>Unable to execute query1select.</p>");


$i=0;
while ($row = mysqli_fetch_assoc($QueryResult1select)) {
	$baseline_data_lock_name[$i] = $row['Name'];
	$baseline_data_lock_bclv[$i] = $row['Best_case_baseline'];
	$i++;
}



$query2 = "select Distinct(Type) as Distinct_types from LOCKS_LOG where Series_id=$test_series";
$QueryResult2 = @mysqli_query($DBConnect, $query2) or die("<p>Unable to execute query2.</p>");

$i=0;
while ($row = mysqli_fetch_assoc($QueryResult2)) {

	if($row['Distinct_types']!='Baseline'){
		$subsys_test_type[$i] = $row['Distinct_types'];
		$i++;
	}
}

?>

<html>
  <head>
  </head>

  <body>


<table>

<?php
for($test_count=0; $test_count<count($subsys_test_type); $test_count++){
?>

<tr>
<!--<td>
    <div align="center" id="">Chart</div>
</td>-->

<td> 

	<div align="" id="Baseline">
   	 <table border=0>
	<tr bgcolor='#64AAD0'><td>Baseline Best Lock Uptime Avg.</td></tr>

	<?php
	for($i=0; $i< count($baseline_data_lock_name);$i++){
	echo "<tr><td>".$baseline_data_lock_name[$i].": ".$baseline_data_lock_bclv[$i]."</td></tr>";	
        }
	?>
   	 </table>
	</div>

</td>
<td>
	
<?php

$no_space_subsys_test_type = str_replace(' ', '', $subsys_test_type[$test_count]); 
$view_name = "BEST_CASE_".$no_space_subsys_test_type."_".$test_series;

$query3view = "create view $view_name as select Name, (MAX(Uptime) - MIN(Uptime))/(select COUNT(Name) from LOCKS_LOG where Series_id=$test_series and Type='$subsys_test_type[$test_count]' and Name='name') +1 as Best_case_subsys from LOCKS_LOG where Series_id=$test_series and Type = '$subsys_test_type[$test_count]' group by Name order by Best_case_subsys DESC";

$QueryResult3view = @mysqli_query($DBConnect, $query3view) or die("<p>Unable to execute query3view33.</p>");

$query3select = "select Name,Best_case_subsys from $view_name";
$QueryResult3select = @mysqli_query($DBConnect, $query3select) or die("<p>Unable to execute query3select.</p>");

$i=0;
while ($row = mysqli_fetch_assoc($QueryResult3select)) {
	$subsys_data_lock_name[$i] = $row['Name'];
	$subsys_data_lock_bclv[$i] = $row['Best_case_subsys'];
	$i++;
}
?>
	<div align="" id="<php? echo $subsys_test_type[$test_count];?>">
   	 <table border=0>
	<tr bgcolor='#64AAD0'><td><?php echo $subsys_test_type[$test_count];?> Test Best Lock Uptime Avg.</td></tr>

	<?php
	for($i=0; $i< count($baseline_data_lock_name);$i++){
	echo "<tr><td>".$subsys_data_lock_name[$i].": ".$subsys_data_lock_bclv[$i]."</td></tr>";	
        }
	?>
   	 </table>
	</div>


</td>	
<td>

<?php

//$query4ratio = "

//(select B.Name, (IFNULL((Best_case_subsys)/(Best_case_baseline),1)) as Ratio from ($view_name_baseline B LEFT JOIN $view_name S on S.Name=B.Name))

//UNION

//(select S.Name, (IFNULL((Best_case_subsys)/(Best_case_baseline),1)) as Ratio from ($view_name_baseline B RIGHT JOIN $view_name S on S.Name=B.Name))

//order by Ratio DESC
//";

$query4ratio = "

(select B.Name, (IFNULL(Best_case_subsys,1)/IFNULL(Best_case_baseline,1)) as Ratio from ($view_name_baseline B LEFT JOIN $view_name S on S.Name=B.Name))

UNION

(select S.Name, (IFNULL(Best_case_subsys,1)/IFNULL(Best_case_baseline,1)) as Ratio from ($view_name_baseline B RIGHT JOIN $view_name S on S.Name=B.Name))

order by Ratio DESC
";

//$QueryResult4ratioview = @mysqli_query($DBConnect, $query4ratioview) or die("<p>Unable to execute query4ratio.</p>");

$QueryResult4ratio = @mysqli_query($DBConnect, $query4ratio) or die("<p>Unable to execute query4ratio.</p>");


$i=0;
while ($row = mysqli_fetch_assoc($QueryResult4ratio)) {
	$subsys_data_lock_name[$i] = $row['Name'];
	$subsys_data_lock_ratio[$i] = $row['Ratio'];
	$i++;
}

?>

	<table border=0>
	<tr bgcolor='#64AAD0'><td><?php echo $subsys_test_type[$test_count];?> Test Best Lock Ratio.</td></tr>

	<?php
	for($i=0; $i< count($baseline_data_lock_name);$i++){
	echo "<tr><td>".$subsys_data_lock_name[$i].": ".$subsys_data_lock_ratio[$i]."</td></tr>";	
        }
	?>
   	 </table>


</td>

</tr>	

<?php
$query3viewdrop = "drop view $view_name";
$QueryResult3viewdrop = @mysqli_query($DBConnect, $query3viewdrop) or die("<p>Unable to execute query3viewdrop.</p>");

}

$query1viewdrop = "drop view $view_name_baseline";
$QueryResult1viewdrop = @mysqli_query($DBConnect, $query1viewdrop) or die("<p>Unable to execute query1select.</p>");


?>

</table>





</body>
</html>
